﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using Domain=XWord.Fundamental.Domain;
using Iesi.Collections.Generic;
using System.Data.Common;
namespace ConsoleApplication1
{
   public class DataTranslate
    {
      

       public static void Run(ISession session)
       {
           FastCategory(session.Connection as DbConnection);
           FastEtyma(session.Connection as DbConnection);
           FastWord(session.Connection as DbConnection);
           FastWordLibrary(session.Connection as DbConnection);
       }

       internal static void FastCategory(DbConnection cnn)
       {
           Console.WriteLine("Category Begin");
           DataClasses1DataContext dc = new DataClasses1DataContext();
           var catList = dc.Categories.ToList();
           var catWordList = dc.CategoryWords.ToList();
           var fixionList = dc.Fixions.ToList();
           using (DbTransaction dbTrans = cnn.BeginTransaction())
           {

               using (DbCommand cmd = cnn.CreateCommand())
               {

                   cmd.CommandText = @"INSERT INTO Category
                         (Id, Name,CategoryType)
                      VALUES  (?,?,?)";

                   DbParameter Field1 = cmd.CreateParameter();
                   DbParameter Field2 = cmd.CreateParameter();
                   DbParameter Field3 = cmd.CreateParameter();
                   cmd.Parameters.Add(Field1);
                   cmd.Parameters.Add(Field2);
                   cmd.Parameters.Add(Field3);
                   Field3.Value = 0;
                   for (int i = 0; i < catList.Count; i++)
                   {
                       Field1.Value = catList[i].Id;
                       Field2.Value = catList[i].Name;
                       cmd.ExecuteNonQuery();
                   }
                   Field3.Value = 1;
                   for (int i = 0; i < fixionList.Count; i++)
                   {
                       Field1.Value = fixionList[i].Id;
                       Field2.Value = fixionList[i].Name;
                       cmd.ExecuteNonQuery();
                   }
                   cmd.CommandText = @"INSERT INTO CategoryWord
                         (CategoryId, WordId)
                      VALUES  (?,?)";
                   for (int i = 0; i < catWordList.Count; i++)
                   {
                       Field1.Value = catWordList[i].CategoryId;
                       Field2.Value = catWordList[i].WordId;
                       cmd.ExecuteNonQuery();
                   }
               }
               dbTrans.Commit();
           }
           Console.WriteLine("Category End");
       }

       internal static void FastEtyma(DbConnection cnn)
       {
           Console.WriteLine("Etyma Begin");
           DataClasses1DataContext dc = new DataClasses1DataContext();
           var etymaList = dc.Etymas.ToList();
           var etymaWordList = dc.EtymaWords.ToList();
           var suffixList = dc.PreSuffixes.ToList();
           using (DbTransaction dbTrans = cnn.BeginTransaction())
           {

               using (DbCommand cmd = cnn.CreateCommand())
               {

                   cmd.CommandText = @"INSERT INTO Etyma
                         (Id, Name, Comment, ParentId,EtymaType)
                          VALUES  (?,?,?,?,?)";

                   DbParameter Field1 = cmd.CreateParameter();
                   DbParameter Field2 = cmd.CreateParameter();
                   DbParameter Field3 = cmd.CreateParameter();
                   DbParameter Field4 = cmd.CreateParameter();
                   DbParameter Field5 = cmd.CreateParameter();
                   cmd.Parameters.Add(Field1);
                   cmd.Parameters.Add(Field2);
                   cmd.Parameters.Add(Field3);
                   cmd.Parameters.Add(Field4);
                   cmd.Parameters.Add(Field5);
                   for (int i = 0; i < etymaList.Count; i++)
                   {
                       Field1.Value = etymaList[i].Id;
                       Field2.Value = etymaList[i].Name;
                       Field3.Value = etymaList[i].Comment;
                       Field4.Value = etymaList[i].ParentId;
                       Field5.Value = typeof(XWord.Fundamental.Domain.Etymon).ToString();
                       cmd.ExecuteNonQuery();
                   }

                   cmd.CommandText = @"INSERT INTO Etyma
                         (Id, Name, Comment, CategoryId,EtymaType)
                          VALUES  (?,?,?,?,?)";
                   for (int i = 0; i < suffixList.Count; i++)
                   {
                       Field1.Value = suffixList[i].Id;
                       Field2.Value = suffixList[i].Name;
                       Field3.Value = suffixList[i].Comment;
                       Field4.Value = suffixList[i].FixionId;
                       Field5.Value = typeof(XWord.Fundamental.Domain.Suffix).ToString();
                       cmd.ExecuteNonQuery();

                   }
                   cmd.Parameters.Clear();
                   cmd.Parameters.Add(Field1);
                   cmd.Parameters.Add(Field2);
                   cmd.CommandText = @"INSERT INTO EtymaWord
                         (EtymaId, WordId)
                      VALUES  (?,?)";
                   for (int i = 0; i < etymaWordList.Count; i++)
                   {
                       Field1.Value = etymaWordList[i].EtymaId;
                       Field2.Value = etymaWordList[i].WordId;
                       cmd.ExecuteNonQuery();
                   }
                 
                  
               }
               dbTrans.Commit();
           }
           Console.WriteLine("Etyma End");
       }

       internal static void FastWord(DbConnection cnn)
       {
           Console.WriteLine("Word Begin");
           DataClasses1DataContext dc = new DataClasses1DataContext();
           var etymaList = dc.Words.ToList();
           using (DbTransaction dbTrans = cnn.BeginTransaction())
           {

               using (DbCommand cmd = cnn.CreateCommand())
               {

                   cmd.CommandText = @"INSERT INTO Word
                         (Id, Name, Comment,IsCET4, IsHighSchool, IsIgnore, IsHighFrequency, WordIndex, Suffix)
                         VALUES (?,?,?,?,?,?,?,?,?)";

                   for (int i = 0; i < 9; i++)
                   {
                       DbParameter Field1 = cmd.CreateParameter();
                       cmd.Parameters.Add(Field1);
                   }
                   for (int i = 0; i < etymaList.Count; i++)
                   {
                       cmd.Parameters[0].Value = etymaList[i].Id;
                       cmd.Parameters[1].Value = etymaList[i].Name;
                       cmd.Parameters[2].Value = etymaList[i].Comment;
                       cmd.Parameters[3].Value = etymaList[i].IsCET4;
                       cmd.Parameters[4].Value = etymaList[i].IsHighSchool;
                       cmd.Parameters[5].Value = etymaList[i].IsIgnore;
                       cmd.Parameters[6].Value = etymaList[i].IsHighFrequency;
                       cmd.Parameters[7].Value = etymaList[i].WordIndex;
                       cmd.Parameters[8].Value = etymaList[i].Suffix;
                       cmd.ExecuteNonQuery();
                   }
               }
               dbTrans.Commit();
           }
           Console.WriteLine("Word End");
       }

       internal static void FastWordLibrary(DbConnection cnn)
       {
           Console.WriteLine("Library Begin");
           DataClasses1DataContext dc = new DataClasses1DataContext();
           var etymaList = dc.EtymaLibraries.ToList();
           var etymaWordList = dc.EtymaWordLibraries.ToList();
           using (DbTransaction dbTrans = cnn.BeginTransaction())
           {
               using (DbCommand cmd = cnn.CreateCommand())
               {

                   cmd.CommandText = @"INSERT INTO EtymaLibrary
                         (Id, Name, Comment,WordIndex)
                         VALUES (?,?,?,?)";

                   for (int i = 0; i < 4; i++)
                   {
                       DbParameter Field1 = cmd.CreateParameter();
                       cmd.Parameters.Add(Field1);
                   }
                   for (int i = 0; i < etymaList.Count; i++)
                   {
                       cmd.Parameters[0].Value = etymaList[i].Id;
                       cmd.Parameters[1].Value = etymaList[i].Name;
                       cmd.Parameters[2].Value = etymaList[i].Comment;
                       cmd.Parameters[3].Value = etymaList[i].WordIndex;
                       cmd.ExecuteNonQuery();
                   }

                   cmd.CommandText = @"INSERT INTO EtymaWordLibrary
                         (Id, Name, WordIndex)
                         VALUES (?,?,?)";

                   for (int i = 0; i < etymaWordList.Count; i++)
                   {
                       cmd.Parameters[0].Value = etymaWordList[i].Id;
                       cmd.Parameters[1].Value = etymaWordList[i].Name;
                       cmd.Parameters[2].Value = etymaWordList[i].WordIndex;
                       cmd.ExecuteNonQuery();
                   }
               }
               dbTrans.Commit();
           }
           Console.WriteLine("Library End");
       }
    }
}
